﻿using MQ.Core.Common;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using Dapper;
using System.Threading.Tasks;

namespace MQ.Core.Db
{
    public static class DbConnectionExtension
    {
        /// <summary>
        /// dapper分页
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="db"></param>
        /// <param name="pagination"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static IEnumerable<T> QueryPagination<T>(this IDbConnection db,Pagination pagination,string sql,object param=null)
        {
            pagination.total = db.ExecuteScalar<int>(GetPageCountSql(sql),param);
            return db.Query<T>(GetPageSql(sql, pagination),param);
        }
        public static DataTable QueryPagination(this IDbConnection db, Pagination pagination, string sql, object param = null)
        {
            pagination.total = db.ExecuteScalar<int>(GetPageCountSql(sql), param);
            var dt = new DataTable();
            var reader = db.ExecuteReader(GetPageSql(sql, pagination), param);
            dt.Load(reader);
            return dt;
        }
        public static async Task<DataTable> QueryPaginationAsync(this IDbConnection db, Pagination pagination, string sql, object param = null)
        {
            pagination.total = db.ExecuteScalar<int>(GetPageCountSql(sql), param);
            var dt = new DataTable();
            var reader = await db.ExecuteReaderAsync(GetPageSql(sql, pagination), param);
            dt.Load(reader);
            return dt;
        }
        /// <summary>
        /// dapper分页 异步
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="db"></param>
        /// <param name="pagination"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static async Task<IEnumerable<T>> QueryPaginationAsync<T>(this IDbConnection db, Pagination pagination, string sql,object param=null)
        {
            pagination.total = await db.ExecuteScalarAsync<int>(GetPageCountSql(sql),param);
            return await db.QueryAsync<T>(GetPageSql(sql,pagination),param);
        }

        private static string GetPageSql(string sql,Pagination pagination)
        {
            StringBuilder sb = new StringBuilder();
            if (pagination.index == 0)
            {
                pagination.index = 1;
            }
            int num = (pagination.index - 1) * pagination.size;
            int num1 = (pagination.index) * pagination.size;
            string OrderBy = "";

            if (!string.IsNullOrEmpty(pagination.field))
            {
                if (pagination.field.ToUpper().IndexOf("ASC") + pagination.field.ToUpper().IndexOf("DESC") > 0)
                {
                    OrderBy = " Order By " + pagination.field;
                }
                else
                {
                    OrderBy = " Order By " + pagination.field + " " + (pagination.orderBy.ToUpper()=="ASC" ? "ASC" : "DESC");
                }
            }
            else
            {
                OrderBy = "order by (select 0)";
            }
            sb.Append("Select * From (Select ROW_NUMBER() Over (" + OrderBy + ")");
            sb.Append(" As rowNum, * From (" + sql + ")  T ) As N Where rowNum > " + num + " And rowNum <= " + num1 + "");
            return sb.ToString();
        }

        private static string GetPageCountSql(string sql)
        {
            sql = $"select count(1) from({sql})t";
            return sql;
        }
    }
}
